#/bin/bash
hostname='192.168.2.52'
port=3306
username='root'
password='123456'
dbname='test'
tbname='user'
sqlfile_source='./tb_source.sql'
sqlfile_destion='./tb_dest.sql'
sqlfile_mod='./t.sql'
tg_file='tg.txt'
qy_file='quyu.txt'

#-----sql cmd---------------------------
#mysqldump -h${hostname} -P${port} -u${username} -p${password}  -d  ${dbname} msc_user_01 > ${sqlfile_source}

#function add(){
#sql_cmd_04='select * from `mysql`.`user`;'
#mysql -h${hostname} -P${port} -u${username} -p${password}  -e "${sql_cmd}"
#mysql -h${hostname} -P${port} -u${username} -p${password}  < ${sqlfile}
#}

function Add_TB(){
    for((i=11;i<200;i++))
        do
        sed  "s/msc_user_01/msc_user_${i}/g" ${sqlfile_source} > ${sqlfile_destion} 
        mysql -h${hostname} -P${port} -u${username} -p${password} ${dbname}  < ${sqlfile_destion}
        if [[ $? -eq 0 ]];then
           echo "${i} add scuccess!"
        else
           break
        fi        
        done
}

#-----------获取城市地区列表---------------------------------------------
function Qy_TB(){
    rm -rf ${qy_file}
    # for i in {'alidiqu','beijingshi',,,,,,,,,,,,,,'bej_10','hb_13','dd_88'}
    #获取统计的数据
    mysql -h${hostname} -P${port} -u${username} -p${passwd} -e 'use mashangdian_zs_db;show tables;' |grep msc |cut -d_ -f1-4|uniq -c|sort -rn  > ${tg_file}
    #-获取区域名称，并写入quyu.txt文件----
    for i in `cat ${tg_file}`
    do
        if [[ $i -ge 14 ]];then
            continue;
        elif [[ $i -eq 1 ]];then
            break;
        else
            echo "${i}" >> ${qy_file}
        fi
     done
    #删除空行
    #sed -i '/^$/d' ${qy_file}
    qy_num=`cat ${qy_file} | wc -l`
    echo "城市数量：${qy_num}"
}
#----------读取区域表文件，并更新表结构--------------------------------
function Mod_TB(){
    # for i in {'alidiqu','beijingshi',,,,,,,,,,,,,,'bej_10','hb_13','dd_88'}
    for i in `cat ${qy_file}`
    do
        sed  "s/msc_user_01/${i}/g" ${sqlfile_source} > ${sqlfile_destion} 
        #sql执行
        #mysql -h${hostname} -P${port} -u${username} -p${password}  < ${sqlfile_destion}
        if [[ $? -eq 0 ]];then
           echo "${i} Update data  scuccess!"
        else
           break;
        fi        
    done
}

#------------比较城市地区表结构的差异------------------------------------
function Diff_TB(){
    one='one.txt'
    two='two.txt'
    mysqldump -h${hostname} -P${port} -u${username} -p${password}  -d  ${dbname} msc_user_01 > ${one}
    for((i=1;i<400;i++))
    do
        #echo "================================================================"
        mysqldump -h${hostname} -P${port} -u${username} -p${password}  -d  ${dbname} msc_user_${i} > ${two}
        #diff $one $two -y
        num=`diff $one $two |wc -l`
        sleep 0.2;
        if [[ $num -le 16 ]];then
            echo "${num} msc_user_01  msc_user_${i} ------- 表结构相同!"
        else
            echo "${num} msc_user_01  msc_user_${i} ++++++++ 表结构不同!"
        fi
    done
  
}

#----------以下函数开始执行-------------------------------------------
Diff_TB





